(c) 2004 Fawcette Technical Publications, Inc.





Listing 1	VB.NET
Maintain Data Consistency With Catch and Rollback
Use a Try/Catch block to determine the transaction's outcome. From within the Catch block, the Rollback method is called when an error occurs. You can maintain data consistency by calling Rollback from the Catch block. This rolls back changes made to data when an error occurs.


Public Function MakeSale(ByVal _
	connectionString As String, _
	ByVal customerID As String, _
	ByVal productID As Integer, _
	ByVal requestedQty As Integer, 
	ByVal unitPrice As Double, _
	ByVal discount As Double) As Boolean _
	Implements ISalesComponent.MakeSale

		Dim conn As New SqlClient.SqlConnection
		Dim successfulSale As Boolean = True
		Dim dataAccess As New DataAccess
		Dim trans As SqlTransaction

		Try

			conn.ConnectionString = _
				connectionString
			conn.Open()

			' Return a transaction object from the 
			' opened connection
			trans = conn.BeginTransaction()

			' The transaction object is passed 
			' between method calls and is used to 
			' manually enlist each SQLCommand.
			successfulSale = _
				dataAccess.newOrder(conn, trans, _
				customerID, productID, _
				requestedQty, unitPrice, discount)

			If successfulSale Then

			successfulSale = _
				dataAccess.adjustInventory(conn, _
				trans, requestedQty, productID)

			If successfulSale Then

				' Check credit card limit. 
				successfulSale = _
					dataAccess.checkCreditCard()

			End If

		End If

		If successfulSale Then
			trans.Commit()
		Else

			trans.Rollback()
			successfulSale = False

		End If

	Catch ex As Exception

		' If an error occurs, rollback transaction
		trans.Rollback()
		Throw ex

	Finally

		MakeSale = successfulSale
		trans.Dispose()

		If conn.State = ConnectionState.Open Then
			conn.Close()
		End If

	End Try

End Function



Listing 2	VB.NET
Auto Enlist-Or Not
In the automatic model, enlistment in the transaction occurs if the SQLConnection object is opened or retrieved from the connection pool. You can disable auto-enlistment by setting the SQLConnection's Enlist property to False.


' Supply the COM+ application name. 
<Assembly: ApplicationName("SalesComponent")> 
' Supply a strong-named assembly.
<Assembly: AssemblyKeyFileAttribute("..\..\TransDemo.snk")> 

Namespace BusinessLogic

' Add transactional support to this class.
<Transaction(TransactionOption.Required)> _
Public Class SalesComponentCOM
	Inherits ServicedComponent
	Implements ISalesComponent

	Public Function MakeSale(ByVal _
		connectionString As String, _
		ByVal customerID As String, _
		ByVal productID As Integer, _
		ByVal requestedQty As Integer, _
		ByVal unitPrice As Double, _
		ByVal discount As Double) _
		As Boolean Implements _
		ISalesComponent.MakeSale

		Dim conn As New SqlClient.SqlConnection
		Dim successfulSale As Boolean = True
		Dim dataAccess As New DataAccess

		Try

			' The SQLConnection object used here
			' is automatically enlisted in the 
			' transaction. A separate 
			' SQLTransaction object isn't needed.
			conn.ConnectionString = _
				connectionString

			' Attempt to add a new order 
			successfulSale = _
				dataAccess.newOrder(conn, _
				customerID, productID, _
				requestedQty, unitPrice, discount)

			If successfulSale Then

				' Attempt to adjust inventory
				successfulSale = _
					ataAccess.adjustInventory(conn, _
					requestedQty, productID)

				If successfulSale Then

					successfulSale = _
						dataAccess.checkCreditCard()

				End If

			End If

			If successfulSale Then

				' If the sale is successful, 
				' commit the transaction
				ContextUtil.SetComplete()

			Else

				' If some part of sale was 
				' unsuccessful, rollback transaction
				ContextUtil.SetAbort()
				successfulSale = False

			End If

		Catch ex As Exception

			' If error occurs, rollback transaction
			ContextUtil.SetAbort()
			Throw ex

		Finally

			MakeSale = successfulSale

		End Try

	End Function

End Class
